package com.cloud.template.base.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.OutputStream;
import java.util.List;

/**
 * 
 * Title: 导出Excel工具类
 * @author zhenghao
 *
 */

public class ExcelExportUtil {
	private static final Logger LOGGER= Logger.getLogger(ExcelExportUtil.class);
	
	/**
	 * 导出Excel
	 * @param sheetName 页签名
	 * @param cellList 列名
	 * @param dataList 数据
	 * @param rowAccessWindowSize 内存中缓存记录数（缓存全部记录‘-1‘）
	 * @param out 输出流
	 */
	public static void writeExcelFile(String sheetName, List<String> cellList, List<List> dataList,
                                      int rowAccessWindowSize, OutputStream out){
		
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
			sheetName = StringUtils.isBlank(sheetName)?"Sheet1":sheetName;
			Sheet sheet = workbook.createSheet(sheetName);
			if (cellList.isEmpty()) {  
	            throw new Exception("cellList不能为空");
	        }
			
			//标题 
			CellStyle titleCellStyle = workbook.createCellStyle();
			titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			titleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			titleCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			titleCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
			
	        Font titleFont = workbook.createFont();
	        titleFont.setFontName("宋体");
			titleFont.setFontHeightInPoints((short)12);
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleCellStyle.setFont(titleFont);
			
			Row rowTitle = sheet.createRow(0);
			Cell cell = null;
			for(int titleNum=0; titleNum<cellList.size();titleNum++){
				cell = rowTitle.createCell(titleNum);
	            cell.setCellStyle(titleCellStyle);
	            cell.setCellValue(cellList.get(titleNum));
	            sheet.setColumnWidth(titleNum, cellList.get(titleNum).getBytes().length*100*4);
			}
			
			//数据内容
			CellStyle dataCellStyle = workbook.createCellStyle();
	        dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        Font colsFont = workbook.createFont();
		    colsFont.setFontName("宋体");
		    colsFont.setFontHeightInPoints((short)11);
		    dataCellStyle.setFont(colsFont);
	        
	        int rowSize = (dataList == null) ? 0 : dataList.size();
	        /*DataFormat format = workbook.createDataFormat();
	        dataCellStyle.setDataFormat(format.getFormat("@"));*/
			
	        for(int i=0;i<rowSize;i++){
	        	Row rowCols = sheet.createRow(i+1);
				Cell dataCell;
				List rowData = dataList.get(i);
				if(rowData == null){
					continue;
				}
				for(int j=0;j<rowData.size();j++){
					dataCell = rowCols.createCell(j);
					dataCell.setCellValue(String.valueOf(rowData.get(j)));
					int dataLength = String.valueOf(rowData.get(j)).getBytes().length*4*95;
                    if (sheet.getColumnWidth(j) < dataLength) {  
                        sheet.setColumnWidth(j, dataLength);  
                    }
					cell.setCellStyle(dataCellStyle);
				}
	        	
	        }
	        workbook.write(out);
	        out.flush();
	        out.close();
	        
		} catch (Exception e) {
			LOGGER.error("生成Excel文件失败",e);
		}
	}
	
}
